using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.Look.Imagem.Entidades;

namespace MakeIT.Look.Imagem.Negocios
{
	public partial class LookDAOT : DAOT
	{

		public BookcliVO getBookcli(int? idBookCli)
		{
			BookcliVO bookcliVO = null;
			List<BookcliVO> listBookcliVOs = getBookclis(idBookCli, null, null, null, null, null, null, null);
			if (listBookcliVOs != null && listBookcliVOs.Count > 0)
			{
				bookcliVO = listBookcliVOs[0];
			}
			return bookcliVO;
		}

		public List<BookcliVO> getBookclis()
		{
			return getBookclis(null, null, null, null, null, null, null, null);
		}

		public List<BookcliVO> getBookclis(int? idBookCli)
		{
			return getBookclis(idBookCli, null, null, null, null, null, null, null);
		}

		public List<BookcliVO> getBookclis(int? idBookCli, int? idPlayList, int? idCliente, string bookCli, DateTime? vigenciaIni, DateTime? vigenciaFin, bool? ativo, bool? publico)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT bcl.idBookCli as bcl_idBookCli, bcl.bookCli as bcl_bookCli, bcl.vigenciaIni as bcl_vigenciaIni, bcl.vigenciaFin as bcl_vigenciaFin, bcl.ativo as bcl_ativo, bcl.idPlayList as bcl_idPlayList, Pll.playlist as Pll_playlist, bcl.idCliente as bcl_idCliente, cli.cliente as cli_cliente, bcl.publico as bcl_publico, bcl.cadastro as bcl_cadastro, bcl.cadastradoPor as bcl_cadastradoPor, bcl.edicao as bcl_edicao, bcl.editadoPor as bcl_editadoPor  ");
			query.AppendLine("FROM bookcli as bcl LEFT OUTER JOIN playlist as Pll on bcl.idPlayList=Pll.idPlayList  LEFT OUTER JOIN cliente as cli on bcl.idCliente=cli.idCliente  ");
			if (idBookCli != null)
			{
				query.AppendLine(where + "bcl.idBookCli=@idBookCli ");
				where = "AND ";
			}
			if (bookCli != null)
			{
				query.AppendLine(where + "bcl.bookCli=@bookCli ");
				where = "AND ";
			}
			if (vigenciaIni != null)
			{
				query.AppendLine(where + "bcl.vigenciaIni=@vigenciaIni ");
				where = "AND ";
			}
			if (vigenciaFin != null)
			{
				query.AppendLine(where + "bcl.vigenciaFin=@vigenciaFin ");
				where = "AND ";
			}
			if (ativo != null)
			{
				query.AppendLine(where + "bcl.ativo=@ativo ");
				where = "AND ";
			}
			if (idPlayList != null)
			{
				query.AppendLine(where + "bcl.idPlayList=@idPlayList ");
				where = "AND ";
			}
			if (idCliente != null)
			{
				query.AppendLine(where + "bcl.idCliente=@idCliente ");
				where = "AND ";
			}
			if (publico != null)
			{
				query.AppendLine(where + "bcl.publico=@publico ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY bcl.idBookCli ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idBookCli!= null) cmd.Parameters.Add(getParameter(cmd, "@idBookCli", idBookCli));
			if (bookCli!= null) cmd.Parameters.Add(getParameter(cmd, "@bookCli", bookCli));
			if (vigenciaIni!= null) cmd.Parameters.Add(getParameter(cmd, "@vigenciaIni", vigenciaIni));
			if (vigenciaFin!= null) cmd.Parameters.Add(getParameter(cmd, "@vigenciaFin", vigenciaFin));
			if (ativo!= null) cmd.Parameters.Add(getParameter(cmd, "@ativo", ativo.Value?"S":"N"));
			if (idPlayList!= null) cmd.Parameters.Add(getParameter(cmd, "@idPlayList", idPlayList));
			if (idCliente!= null) cmd.Parameters.Add(getParameter(cmd, "@idCliente", idCliente));
			if (publico!= null) cmd.Parameters.Add(getParameter(cmd, "@publico", publico.Value?"S":"N"));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<BookcliVO> listBookcli = new List<BookcliVO>();

				while (reader.Read())
				{
					BookcliVO bookcliVO = BookcliVO.CreateVO();
					bookcliVO.idBookCli = reader.GetInt32(reader.GetOrdinal("bcl_idBookCli"));
					bookcliVO.bookCli = reader.GetString(reader.GetOrdinal("bcl_bookCli"));
					bookcliVO.vigenciaIni = reader.GetDateTime(reader.GetOrdinal("bcl_vigenciaIni"));
					bookcliVO.vigenciaFin = reader.GetDateTime(reader.GetOrdinal("bcl_vigenciaFin"));
					bookcliVO.ativo = reader.GetString(reader.GetOrdinal("bcl_ativo")).Equals("S");
					if (!reader.IsDBNull(reader.GetOrdinal("bcl_idPlayList")))
						bookcliVO.idPlayList = reader.GetInt32(reader.GetOrdinal("bcl_idPlayList"));
					if (!reader.IsDBNull(reader.GetOrdinal("Pll_playlist")))
						bookcliVO.idPlayListVO.playlist = reader.GetString(reader.GetOrdinal("Pll_playlist"));
					bookcliVO.idCliente = reader.GetInt32(reader.GetOrdinal("bcl_idCliente"));
					if (!reader.IsDBNull(reader.GetOrdinal("cli_cliente")))
						bookcliVO.idClienteVO.cliente = reader.GetString(reader.GetOrdinal("cli_cliente"));
					bookcliVO.publico = reader.GetString(reader.GetOrdinal("bcl_publico")).Equals("S");
					bookcliVO.cadastro = reader.GetDateTime(reader.GetOrdinal("bcl_cadastro"));
					bookcliVO.cadastradoPor = reader.GetString(reader.GetOrdinal("bcl_cadastradoPor"));
					bookcliVO.edicao = reader.GetDateTime(reader.GetOrdinal("bcl_edicao"));
					bookcliVO.editadoPor = reader.GetString(reader.GetOrdinal("bcl_editadoPor"));


					listBookcli.Add(bookcliVO);
				}
				reader.Close();
				return listBookcli;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setBookcli(BookcliVO bookcliVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE bookcli ");
			query.AppendLine("SET bookCli=@bookCli, vigenciaIni=@vigenciaIni, vigenciaFin=@vigenciaFin, ativo=@ativo, idPlayList=@idPlayList, idCliente=@idCliente, publico=@publico,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idBookCli=@idBookCli ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookCli", bookcliVO.idBookCli));
			cmd.Parameters.Add(getParameter(cmd, "@bookCli", bookcliVO.bookCli));
			cmd.Parameters.Add(getParameter(cmd, "@vigenciaIni", bookcliVO.vigenciaIni));
			cmd.Parameters.Add(getParameter(cmd, "@vigenciaFin", bookcliVO.vigenciaFin));
			cmd.Parameters.Add(getParameter(cmd, "@ativo", (bookcliVO.ativo==null?" ":(bookcliVO.ativo.Value?"S":"N"))));
			if (bookcliVO.idPlayList == null)
				cmd.Parameters.Add(getParameter(cmd, "@idPlayList", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idPlayList", bookcliVO.idPlayList));
			cmd.Parameters.Add(getParameter(cmd, "@idCliente", bookcliVO.idCliente));
			cmd.Parameters.Add(getParameter(cmd, "@publico", (bookcliVO.publico==null?" ":(bookcliVO.publico.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", bookcliVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", bookcliVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", bookcliVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", bookcliVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newBookcli(BookcliVO bookcliVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO bookcli ");
			query.AppendLine("  ( bookCli, vigenciaIni, vigenciaFin, ativo, idPlayList, idCliente, publico, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @bookCli, @vigenciaIni, @vigenciaFin, @ativo, @idPlayList, @idCliente, @publico, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@bookCli", bookcliVO.bookCli));
			cmd.Parameters.Add(getParameter(cmd, "@vigenciaIni", bookcliVO.vigenciaIni));
			cmd.Parameters.Add(getParameter(cmd, "@vigenciaFin", bookcliVO.vigenciaFin));
			cmd.Parameters.Add(getParameter(cmd, "@ativo", (bookcliVO.ativo==null?" ":(bookcliVO.ativo.Value?"S":"N"))));
			if (bookcliVO.idPlayList == null)
				cmd.Parameters.Add(getParameter(cmd, "@idPlayList", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idPlayList", bookcliVO.idPlayList));
			cmd.Parameters.Add(getParameter(cmd, "@idCliente", bookcliVO.idCliente));
			cmd.Parameters.Add(getParameter(cmd, "@publico", (bookcliVO.publico==null?" ":(bookcliVO.publico.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", bookcliVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", bookcliVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", bookcliVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", bookcliVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('bookcli') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delBookcli(BookcliVO bookcliVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM bookcli ");
			query.AppendLine("WHERE idBookCli=@idBookCli ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookCli", bookcliVO.idBookCli));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountBookclixIdPlayList(int? idPlayList)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idPlayList) as ocorrencias FROM bookcli WHERE IdPlayList=@IdPlayList ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdPlayList", idPlayList));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountBookclixIdCliente(int? idCliente)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCliente) as ocorrencias FROM bookcli WHERE IdCliente=@IdCliente ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCliente", idCliente));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
